import pandas as pd
import os
from config.config import config_params
import util.env_utils as env_utils
import util.log_utils as log_utils
import util.db_utils as db_utils

__excel_file_path = os.path.join(env_utils.project_root, config_params['excel_file_path'])
__logger = log_utils.setup_logger(env_utils.get_relative_path_and_name(__file__))

def import_to_t_work_hour_records_from_excel():            
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 读取 Excel 文件
    df = pd.read_excel(__excel_file_path, sheet_name='工时')
    v_rows = df.values.tolist()

    # 对 reported_hours 字段做四舍五入处理，保留两位小数
    v_time_report_time_index = 10  # 根据插入语句中字段顺序确定 time_report_time 索引
    v_time_belong_date_index = 11  # 根据插入语句中字段顺序确定 time_belong_date 索引
    v_reported_hours_index = 12  # 根据插入语句中字段顺序确定 reported_hours 索引
    v_approved_hours_index = 13  # 根据插入语句中字段顺序确定 approved_hours 索引
    v_approval_time_index = 14  # 根据插入语句中字段顺序确定 approval_time 索引
    v_batch_size = 1000
    for v_i in range(0, len(v_rows), v_batch_size):
        v_batch = v_rows[v_i:v_i + v_batch_size]
        for v_row in v_batch:
            if pd.isna(v_row[v_time_report_time_index]):
                v_row[v_time_report_time_index] = None
            if pd.isna(v_row[v_time_belong_date_index]):
                v_row[v_time_belong_date_index] = None
            if not pd.isna(v_row[v_reported_hours_index]):
                v_row[v_reported_hours_index] = round(float(v_row[v_reported_hours_index]), 2)
            else:
                v_row[v_reported_hours_index] = None
            if not pd.isna(v_row[v_approved_hours_index]):
                v_row[v_approved_hours_index] = round(float(v_row[v_approved_hours_index]), 2)
            else:
                v_row[v_approved_hours_index] = None
            if pd.isna(v_row[v_approval_time_index]):
                v_row[v_approval_time_index] = None

        v_insert_query = '''
        INSERT INTO t_work_hour_records (
            organization, organization_code, employee_name, employee_code, department,
            task_name, project_manager, project_name, project_code, task_type,
            time_report_time, time_belong_date, reported_hours, approved_hours,
            approval_time, other_notes, processing_notes, from_source
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        '''
        try:
            cursor.executemany(v_insert_query, v_batch)
            conn.commit()
            v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
            __logger.info(f'工时信息批次数据导入成功，批次范围：{v_i + 1} - {v_end_index + 1}')
        except Exception as e:
            conn.rollback()
            v_error_row_index = 0
            for v_index, v_row in enumerate(v_batch):
                try:
                    cursor.execute(v_insert_query, v_row)
                except Exception as inner_e:
                    v_error_row_index = v_i + v_index
                    v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
                    __logger.error(f'工时信息批次数据导入失败，当前批次: {v_i // v_batch_size + 1}，开始记录数: {v_i + 1}，结束记录数: {v_end_index + 1}，错误数据所在行: {v_error_row_index + 2}（包含标题行），错误信息: {inner_e}')
                    __logger.error(f'错误的行数据如下： {v_row}')
                    break
            conn.rollback()

# 新增方法：执行 UPDATE SQL 语句
def execute_workhour_data():
    __logger.info('execute_workhour_data')
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    try:
        v_update_sql = '''
        update t_work_hour_records 
   set ex_financial_month =
    case 
		when EXTRACT(day FROM time_belong_date)>25 then (EXTRACT(year FROM time_belong_date)*100+EXTRACT(month FROM time_belong_date) + 1)
		else (EXTRACT(year FROM time_belong_date)*100+EXTRACT(month FROM time_belong_date))
	end,
	ex_working_hours = 
	case 
		when approved_hours is null then reported_hours
		else approved_hours
	end,
	ex_working_days = 
	case 
		when approved_hours is null then round(reported_hours/8,2)
		else round(approved_hours/8,2)
	end,
	ex_employee_company = 
	case 
		when employee_code is not null and employee_code <> 'NaN' then (select t_employee_info.organization from t_employee_info where t_employee_info.employee_code = t_work_hour_records.employee_code)
		else null
	end,
	ex_employee_department = 
	case 
		when employee_code is not null and employee_code <> 'NaN' then (select t_employee_info.department from t_employee_info where t_employee_info.employee_code = t_work_hour_records.employee_code)
		else null
	end,
	ex_project_company = 
	case 
		when project_code is not null and project_code <> 'NaN' then (select t_project_info.organization from t_project_info where t_project_info.project_code = t_work_hour_records.project_code)
		else null
	end
where 1=1;
        '''
        
        cursor.execute(v_update_sql)
        conn.commit()
        #__logger.info(f'UPDATE SQL 语句执行成功: {v_update_sql}')
        __logger.info(f'UPDATE SQL 语句执行成功')
    except Exception as e:
        conn.rollback()
        __logger.error(f'UPDATE SQL 语句执行失败: {v_update_sql}, 错误信息: {e}')
    